package servletmvc.imooc.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import java.util.List;

import servletmvc.imooc.dao.UserDao;
import servletmvc.imooc.entity.User;
import servletmvc.imooc.util.DBUtil;

public class UserDaoImpl implements UserDao{

	@Override
	public List<User> queryUserList() {
		List<User> userList = new ArrayList<User>();
		//连接数据库
		Connection conn = DBUtil.getConnection();
		String sql = "select * from tb_user";
		try {
			PreparedStatement pt = conn.prepareStatement(sql);
			//执行sql语句,并接收结果集
			ResultSet rt = pt.executeQuery();
			while(rt.next()) {
				User user = new User();
				user.setId(rt.getInt("id"));
				user.setUsername(rt.getString("username"));
				user.setPassword(rt.getString("password"));
				user.setName(rt.getString("name"));
				user.setGender(rt.getString("gender"));
				user.setInfo(rt.getString("info"));
				user.setCreateTime(rt.getTimestamp("create_time"));
				user.setLastEditTime(rt.getTimestamp("last_edit_time"));
				//放进集合里
				userList.add(user);
			}
			//关闭流
			rt.close();
			pt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public int addUser(User user) {
		int effectNum = 0;
		Connection conn = DBUtil.getConnection();
		String sql = "INSERT INTO tb_user(username,password,name,gender,info,create_time,last_edit_time) value (?,?,?,?,?,?,?)";
		try {
			PreparedStatement pt = conn.prepareStatement(sql);
			pt.setString(1, user.getUsername());
			pt.setString(2, user.getPassword());
			pt.setString(3, user.getName());
			pt.setString(4, user.getGender());
			pt.setString(5, user.getInfo());
			/*
			 * String cTime = DateFormat.getDateInstance(DateFormat.FULL).format(new
			 * Date()).toString(); String lTime =
			 * DateFormat.getDateInstance(DateFormat.FULL).format(new Date()).toString();
			 */		
			pt.setDate(6, new Date(System.currentTimeMillis()));
			pt.setDate(7, new Date(System.currentTimeMillis()));
			effectNum = pt.executeUpdate();//执行sql语句
			if(effectNum > 0) {
				System.out.println("数据插入成功!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return effectNum;
	}

	@Override
	public int deleteUserById(Integer id) {
		int effectedNum = 0;
		Connection conn = DBUtil.getConnection();
		String sql = "DELETE FROM tb_user WHERE id = ?";
		try {
			PreparedStatement pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			effectedNum = pt.executeUpdate();
			pt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return effectedNum;
	}

	@Override
	public int updateUserById(User user) {
		int effectedNum = 0;
		Connection conn = DBUtil.getConnection();
		String sql = "UPDATE tb_user SET username = ?, password = ?, name = ?, "
				+ "gender = ?, info = ?, last_edit_time = ? WHERE id = ?";
		try {
			PreparedStatement pt = conn.prepareStatement(sql);
			pt.setString(1, user.getUsername());
			pt.setString(2, user.getPassword());
			pt.setString(3, user.getName());
			pt.setString(4, user.getGender());
			pt.setString(5, user.getInfo());
			pt.setDate(6, new Date(System.currentTimeMillis()));
			pt.setInt(7, user.getId());
			effectedNum = pt.executeUpdate();
			pt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return effectedNum;
	}

	@Override
	public User queryById(Integer id) {
		User user = new User();
		Connection conn = DBUtil.getConnection();
		String sql = "SELECT * FROM tb_user WHERE id = ?";
		try {
			PreparedStatement pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			ResultSet rt = pt.executeQuery();
			while(rt.next()) {
				user.setId(rt.getInt("id"));
				user.setUsername(rt.getString("username"));
				user.setPassword(rt.getString("password"));
				user.setName(rt.getString("name"));
				user.setGender(rt.getString("gender"));
				user.setInfo(rt.getString("info"));
				user.setCreateTime(rt.getDate("create_time"));
				user.setLastEditTime(rt.getDate("last_edit_time"));
			}
			rt.close();
			pt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return user;
	}

	@Override
	public int batchInsertUser(List<User> userList) {
		int effectedNum = 0;
		Connection conn = DBUtil.getConnection();
		String sql = "INSERT INTO tb_user(username, password, name, gender, info, create_time, last_edit_time) VALUES (?,?,?,?,?,?,?)";
		try {
			PreparedStatement pt = null;
			for(User user : userList) {
				pt = conn.prepareStatement(sql);
				pt.setString(1, user.getUsername());
				pt.setString(2, user.getPassword());
				pt.setString(3, user.getName());
				pt.setString(4, user.getGender());
				pt.setString(5, user.getInfo());
				pt.setDate(6, new Date(System.currentTimeMillis()));
				pt.setDate(7, new Date(System.currentTimeMillis()));
				effectedNum = pt.executeUpdate();
			}
			pt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return effectedNum;
	}

}
